package dao;

import java.util.Vector;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;

public class Bird extends DAOBase {
        /* The columns in the table that correspond to the attributes of a Bird
         * object. Used when building SQL queries.
         */
        public static final String staticFields =
                "Bird.scientific_name, " +
                "Bird.size, " +
                "Bird.risk_of_extinction, " +
                "Bird.weight, " +
                "Bird.general_information, " +
                "Bird.map";

        private String scientificName;
        private float size;
        private String riskOfExtinction;
        private float weight;
        private String generalInformation;
        private String map;

        public Bird(String scientificName, float size, String riskOfExtinction,
                    float weight, String generalInformation, String map)
        {
                super(scientificName);

                this.scientificName = scientificName;
                this.size = size;
                this.riskOfExtinction = riskOfExtinction;
                this.weight = weight;
                this.generalInformation = generalInformation;
                this.map = map;
        }

        /* Commit a bird from memory to the database. */
        public void insert()
        throws SQLException
        {
                String SQLInsert;

                SQLInsert = String.format("INSERT INTO Bird VALUES " +
                                          "(%s, %s, %s, %s, %s, %s)",
                                          quoteForSQL(scientificName),
                                          quoteForSQL(size),
                                          quoteForSQL(riskOfExtinction),
                                          quoteForSQL(weight),
                                          quoteForSQL(generalInformation),
                                          quoteForSQL(map));

                ConnectionManager.getStatement().executeUpdate(SQLInsert);
        }

        /* Update the database with information from this bird. */
        public void update()
        throws SQLException
        {
                String SQLUpdate;

                SQLUpdate = String.format("UPDATE Bird SET " +
                                          "scientific_name=%s, " +
                                          "size=%s, " +
                                          "risk_of_extinction=%s, " +
                                          "weight=%s, " +
                                          "general_information=%s, " +
                                          "map = %s " +
                                          "WHERE scientific_name = %s",
                                          quoteForSQL(scientificName),
                                          quoteForSQL(size),
                                          quoteForSQL(riskOfExtinction),
                                          quoteForSQL(weight),
                                          quoteForSQL(generalInformation),
                                          quoteForSQL(map),
                                          quoteForSQL(scientificName));

                ConnectionManager.getStatement().executeUpdate(SQLUpdate);
        }

        /* Convenience function: update the database if this object is already
         * stored, else add it.
         */
        public void commit()
        throws SQLException
        {
                if (Bird.byScientificName(scientificName) != null) {
                        update();
                } else {
                        insert();
                }
        }

        /* Remove a bird from the database. */
        public void remove()
        throws SQLException
        {
                Statement command;

                command = ConnectionManager.getStatement();

                /* Remove entries from dependent tables */
                for (String food : getFood()) {
                        removeFood(food);
                }

                for (String picture : getPictures()) {
                        removePicture(Integer.parseInt(picture));
                }

                for (String name : getPopularNames()) {
                        removePopularName(name);
                }

                command.executeUpdate("DELETE FROM Bird " +
                                      "WHERE scientific_name = " +
                                      quoteForSQL(scientificName));
        }

        /* Getters / setters */
        public String getMap()
        {
                return map;
        }

        public String getGeneralInformation()
        {
                return generalInformation;
        }

        public float getWeight()
        {
                return weight;
        }

        public String getRiskOfExtinction()
        {
                return riskOfExtinction;
        }

        public float getSize()
        {
                return size;
        }

        public String getScientificName()
        {
                return scientificName;
        }

        public Vector<String> getFood()
        throws SQLException
        {
                return getExternalField("Food", "type_of_food",
                                        "Bird_scientific_name");
        }

        public Vector<String> getPopularNames()
        throws SQLException
        {
                return getExternalField("Popular_Names", "name",
                                        "Bird_scientific_name");
        }

        /* XXX Weird, getPictures returns Strings and addPicture receives
         * an integer!
         */
        public Vector<String> getPictures()
        throws SQLException
        {
                return getExternalField("Pictures", "idPicture",
                                        "Bird_scientific_name");
        }

        public void setScientificName(String scientificName)
        {
                /* XXX Creates inconsistencies in foreign keys? */
                this.scientificName = scientificName;
        }

        public void setSize(float size)
        {
                this.size = size;
        }

        public void setWeight(float weight)
        {
                this.weight = weight;
        }

        public void setGeneralInformation(String generalInformation)
        {
                this.generalInformation = generalInformation;
        }

        public void setMap(String map)
        {
                this.map = map;
        }

        /* Add a food entry to this bird.
         */
        public void addFood(String food)
        throws SQLException
        {
                Object values[] = {food, scientificName};
                addExternalField("Food", values);
        }

        /* Add a popular name to this bird.
         */
        public void addPopularName(String name)
        throws SQLException
        {
                Object values[] = {name, scientificName};
                addExternalField("Popular_Names", values);
        }

        /* Add a picture to this bird. */
        public void addPicture(int idPicture, String date)
        throws SQLException
        {
                Object values[] = {idPicture, scientificName, date};
                addExternalField("Pictures", values);
        }

        /* Remove a food entry for this bird. */
        public void removeFood(String food)
        throws SQLException
        {
                removeExternalField("Food", "type_of_food",
                                    "Bird_scientific_name", food);
        }

        /* Remove a popular name for this bird. */
        public void removePopularName(String name)
        throws SQLException
        {
                removeExternalField("Popular_Names", "name",
                                    "Bird_scientific_name", name);
        }

        /* Remove a picture for this bird. */
        public void removePicture(int idPicture)
        throws SQLException
        {
                removeExternalField("Pictures", "idPicture",
                                    "Bird_scientific_name", idPicture);
        }

        /* Queries */

        /* Returns the Bird whose scientific name matches the argument, or null
         * if none is found.
         * Since the scientific name is the primary key, only one result is to
         * be expected.
         * If multiple results are found, a warning is issued to standard error
         * and the first is returned.
         */
        public static Bird byScientificName(String scientificName)
        throws SQLException
        {
                String SQLQuery;
                Vector<Bird> result;

                SQLQuery = String.format("SELECT %s " +
                                         "FROM Bird " +
                                         "WHERE Bird.scientific_name = %s",
                                         staticFields, quoteForSQL(scientificName));

                result = runSQLQuery(SQLQuery);
                if (result.size() > 1) {
                        System.err.println("WARNING: Duplicated primary key " +
                                           scientificName);
                }

                return result.size() != 0 ? result.firstElement() : null;
        }

        /* Query birds by their food type.
         * Returns a Vector of Bird objects whose food type matches the
         * argument.
         */
        public static Vector<Bird> byFood(String food)
        throws SQLException
        {
                String SQLQuery;

                SQLQuery = String.format("SELECT %s " +
                                         "FROM Bird, Food " +
                                         "WHERE " +
                                         "Food.Bird_scientific_name = " +
                                         "Bird.scientific_name " +
                                         "AND Food.type_of_food = %s",
                                         staticFields, quoteForSQL(food));

                return runSQLQuery(SQLQuery);
        }

        /* Query birds by their size.
         * Returns a Vector of Bird objects whose size matches the
         * argument.
         */
        public static Vector<Bird> bySize(float size)
        throws SQLException
        {
                return runSQLQuery("SELECT " + staticFields + " " +
                                   "FROM Bird " +
                                   "WHERE Bird.size = " + size);
        }

        /* Query birds by their weight.
         * Returns a Vector of Bird objects whose weight matches the
         * argument.
         */
        public static Vector<Bird> byWeight(float weight)
        throws SQLException
        {
                return runSQLQuery("SELECT " + staticFields + " " +
                                   "FROM Bird " +
                                   "WHERE Bird.weight = " + weight);
        }

        /* Query birds by their general information.
         * Returns a Vector of Bird objects whose general information matches
         * the argument.
         */
        public static Vector<Bird> byGeneralInformation(String info)
        throws SQLException
        {
                return runSQLQuery("SELECT " + staticFields + " " +
                                   "FROM Bird " +
                                   "WHERE Bird.general_information = " +
                                   quoteForSQL(info));
        }

        /* Query birds by their map.
         * Returns a Vector of Bird objects whose map matches
         * the argument.
         */
        public static Vector<Bird> byMap(String map)
        throws SQLException
        {
                return runSQLQuery("SELECT " + staticFields + " " +
                                   "FROM Bird " +
                                   "WHERE Bird.map = " +
                                   quoteForSQL(map));
        }

        /* Query birds by their popular name.
         * Returns a Vector of Bird objects whose popular name matches the
         * argument.
         */
        public static Vector<Bird> byPopularName(String popularName)
        throws SQLException
        {
                return runSQLQuery("SELECT " + staticFields + " " +
                                   "FROM Bird, Popular_Names " +
                                   "WHERE " +
                                   "Popular_Names.Bird_scientific_name = " +
                                   "Bird.scientific_name " +
                                   "AND Popular_Names.name = " +
                                   quoteForSQL(popularName));
        }

        /* Query birds by their pictures.
         * Returns a Vector of Bird objects whose pictures match the arguments.
         */
        public static Vector<Bird> byPicture(int idPicture)
        throws SQLException
        {
                return runSQLQuery("SELECT " + staticFields + " " +
                                   "FROM Bird, Pictures " +
                                   "WHERE " +
                                   "Pictures.Bird_scientific_name = " +
                                   "Bird.scientific_name " +
                                   "AND Pictures.idPicture = " +
                                   idPicture);
        }

        /* Run a SQL query and get the result as a Vector of Bird objects.
         * The query must return all fields from the Bird table, which are
         * accessible in the comma-separated string Bird.staticFields.
         *
         * Note: You won't generally want to use this function to build Birds.
         * Please stick with the simpler query methods whenever possible.
         */
        public static Vector<Bird> runSQLQuery(String SQLQuery)
        throws SQLException
        {
                ResultSet result;
                Statement statement;
                Vector<Bird> birds = new Vector<Bird>();

                statement = ConnectionManager.getStatement();
                result = statement.executeQuery(SQLQuery);

                while (result.next()) {
                        Bird b;

                        b = new Bird(result.getString("scientific_name"),
                                     result.getFloat("size"),
                                     result.getString("risk_of_extinction"),
                                     result.getFloat("weight"),
                                     result.getString("general_information"),
                                     result.getString("map"));

                        birds.add(b);
                }

                return birds;
        }

        /* Useful stuff, don't touch... */
        public static final String[]
        TypeOfFood = {"Birdseed",
                      "Fruits",
                      "Insects"};

        public static final String[]
        Habitat = {"Amazônia",
                   "Caatinga",
                   "Cerrado",
                   "Pantanal",
                   "Pradarias"};

        public static final String[]
        CrestColor = {"Red",
                      "Green",
                      "Yellow",
                      "Blue",
                      "Black",
                      "White"};

        public static final String[]
        HeadColor = {"Red",
                     "Green",
                     "Yellow",
                     "Blue",
                     "Black",
                     "White"};

        public static final String[]
        BodyColor = {"Red",
                     "Green",
                     "Yellow",
                     "Blue",
                     "Black",
                     "White"};

        public static final String[]
        TailColor = {"Red",
                     "Green",
                     "Yellow",
                     "Blue",
                     "Black",
                     "White"};

        public static final String[]
        RiskOfExtinction = {"Extinct",
                            "Extinct in the Wild",
                            "Critically Endangered",
                            "Endangered",
                            "Vulnerable",
                            "Near Threatened",
                            "Least Concern"};
}
